package com.hzzh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.hzzh.util.ConnectionFactory;
import com.hzzh.vo.Employee;
import com.hzzh.vo.Meeting;

public class MeetingParticipantsDAO {

	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;

	// 向表meetingparticipants中插入记录
	public void insert(int meetingid, int employeeid) {
		conn = ConnectionFactory.getConnection();
		String sql = "insert into meetingparticipants values(?,?)";
		try {
			ps = conn.prepareStatement(sql);
			ps.setInt(1, meetingid);
			ps.setInt(2, employeeid);

			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionFactory.closeConnection(rs, ps, conn);
		}

	}

	// 查询某员工参加的所有会议
	public List<Meeting> selectAllMeetingsByParId(int participantsid) {
		conn = ConnectionFactory.getConnection();
		List<Meeting> meetingslist = new ArrayList<Meeting>();
		Meeting meeting = null;
		try {
			String sql = "select * from meeting,meetingparticipants where meeting.meetingid=meetingparticipants.meetingid and meetingparticipants.employeeid="
					+ participantsid;
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				meeting = new Meeting();
				meeting.setMeetingid(rs.getInt("meetingid"));
				meeting.setMeetingname(rs.getString("meetingname"));
				meeting.setRoomid(rs.getInt("roomid"));
				meeting.setReservationistid(rs.getInt("reservationistid"));
				meeting.setNumberofparticipants(rs.getInt("numberofparticipants"));
				meeting.setStarttime(rs.getTimestamp("starttime"));
				meeting.setEndtime(rs.getTimestamp("endtime"));
				meeting.setReservationtime(rs.getTimestamp("reservationtime"));
				meeting.setCanceledtime(rs.getTimestamp("canceledtime"));
				meeting.setDescription(rs.getString("description"));
				meeting.setStatus(rs.getString("status"));
				meetingslist.add(meeting);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.closeConnection(rs, ps, conn);
		}
		return meetingslist;
	}

	// 查询某会议中所有的参会员工
	public List<Employee> selectAllEmployeesByMeetingId(int meetingid) {
		conn = ConnectionFactory.getConnection();
		List<Employee> employeeslist = new ArrayList<Employee>();
		Employee employee = null;
		try {
			String sql = "select * from employee,meetingparticipants where employee.employeeid=meetingparticipants.employeeid and meetingparticipants.meetingid="
					+ meetingid;
			ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				employee = new Employee();
				employee.setEmployeeid(rs.getInt("employeeid"));
				employee.setEmployeename(rs.getString("employeename"));
				employee.setUsername(rs.getString("username"));
				employee.setPhone(rs.getString("phone"));
				employee.setEmail(rs.getString("email"));
				employee.setStatus(rs.getString("status"));
				employee.setDepartmentid(rs.getInt("departmentid"));
				employee.setPassword(rs.getString("password"));
				employee.setRole(rs.getString("role"));
				employeeslist.add(employee);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.closeConnection(rs, ps, conn);
		}
		return employeeslist;

	}

	// 查询某员工最近七天参加的所有会议
	public List<Meeting> selectAllNewMeetings(int participantsid) {
		conn = ConnectionFactory.getConnection();
		List<Meeting> meetingslist = new ArrayList<Meeting>();
		Meeting meeting = null;
		try {
			// 获取当前系统时间
			Timestamp now = new Timestamp(System.currentTimeMillis());
			// 七天后的时间
			Timestamp sevenDays = new Timestamp(System.currentTimeMillis() + 7 * 24 * 3600 * 1000);
			String nowTime = now.toString();
			String sevenDaysTime = sevenDays.toString();

			String sql = "select * from meeting,meetingparticipants where meeting.meetingid=meetingparticipants.meetingid "
					+ "and meetingparticipants.employeeid=? and to_char(meeting.starttime,'YYYY-MM-DD hh24:mi:ss')>'"+nowTime+"' and to_char(meeting.starttime,'YYYY-MM-DD hh24:mi:ss')<'"+sevenDaysTime+"' and status='0'";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, participantsid);
			rs = ps.executeQuery();
			while (rs.next()) {
				meeting = new Meeting();
				meeting.setMeetingid(rs.getInt("meetingid"));
				meeting.setMeetingname(rs.getString("meetingname"));
				meeting.setRoomid(rs.getInt("roomid"));
				meeting.setReservationistid(rs.getInt("reservationistid"));
				meeting.setNumberofparticipants(rs.getInt("numberofparticipants"));
				meeting.setStarttime(rs.getTimestamp("starttime"));
				meeting.setEndtime(rs.getTimestamp("endtime"));
				meeting.setReservationtime(rs.getTimestamp("reservationtime"));
				meeting.setCanceledtime(rs.getTimestamp("canceledtime"));
				meeting.setDescription(rs.getString("description"));
				meeting.setStatus(rs.getString("status"));
				meetingslist.add(meeting);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.closeConnection(rs, ps, conn);
		}
		return meetingslist;
	}

	// 查询某员工被要求参加，但是又取消的会议
	public List<Meeting> selectAllCanceledMeetings(int participantsid) {
		conn = ConnectionFactory.getConnection();
		List<Meeting> meetingslist = new ArrayList<Meeting>();
		Meeting meeting = null;
		try {
			Timestamp now = new Timestamp(System.currentTimeMillis());
			String nowTime = now.toString();

			String sql = "select * from meeting,meetingparticipants where meeting.meetingid=meetingparticipants.meetingid "
					+ "and meetingparticipants.employeeid=? and to_char(meeting.starttime,'YYYY-MM-DD')>'"+nowTime+"' and status='1'";
			ps = conn.prepareStatement(sql);
			ps.setInt(1, participantsid);
			rs = ps.executeQuery();
			while (rs.next()) {
				meeting = new Meeting();
				meeting.setMeetingid(rs.getInt("meetingid"));
				meeting.setMeetingname(rs.getString("meetingname"));
				meeting.setRoomid(rs.getInt("roomid"));
				meeting.setReservationistid(rs.getInt("reservationistid"));
				meeting.setNumberofparticipants(rs.getInt("numberofparticipants"));
				meeting.setStarttime(rs.getTimestamp("starttime"));
				meeting.setEndtime(rs.getTimestamp("endtime"));
				meeting.setReservationtime(rs.getTimestamp("reservationtime"));
				meeting.setCanceledtime(rs.getTimestamp("canceledtime"));
				meeting.setDescription(rs.getString("description"));
				meeting.setStatus(rs.getString("status"));
				meetingslist.add(meeting);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			ConnectionFactory.closeConnection(rs, ps, conn);
		}
		return meetingslist;
	}

}
